package smartfleet.mobile;


import java.util.ArrayList;
import java.util.Date;

import smartfleet.mobile.Protocol.BdMsg;
import smartfleet.mobile.Protocol.Station;
import smartfleet.mobile.Protocol.Traveller;
import smartfleet.mobile.Protocol.Trip;
import smartfleet.mobile.Protocol.location;
import smartfleet.mobile.Protocol.vehicle;
import smartfleet.mobile.vehicle.Vehicle;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.util.Log;

public class dbAdapter {

	private int _id;
	
	
	private static final String LOCATION_TABLE = "location";
    private static final String KEY_ID = "_id";
    private static final String KEY_NAME = "name";
    private static final String KEY_LON = "longitude";
    private static final String KEY_LAT = "latitude";
    
    private static final String STATION_TABLE = "station";
    private static final String KEY_LOCATIONID = "location_id";

    private static final String VEHICLE_TABLE = "vehicle";
    private static final String KEY_BATT = "battery";
    private static final String KEY_STATE = "state";
    private static final String KEY_SEATS = "seats";
    private static final String KEY_ALT = "altitude";
    private static final String KEY_DESTID = "destination_id";
    private static final String KEY_TIME = "time";
    
    private static final String TRAVELLER_TABLE = "traveller";
    private static final String KEY_SIZE = "size";
    private static final String KEY_CHECKIN = "checkin";
    private static final String KEY_CHECKOUT = "checkout";
    private static final String KEY_STATIONID = "station_id";
    
    private static final String TRIP_TABLE = "trip";
    private static final String KEY_VEHICLEID = "vehicle_id";
    private static final String KEY_TRAVELLERID = "traveller_id";
    
    private static final String TAG = "dbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    /**
     * Database creation sql statement
     */
    private static final String CREATE_LOCATION = 
    	"create table "+LOCATION_TABLE+" ("+KEY_ID+" integer primary key autoincrement, "
        +KEY_NAME+" text not null, "+KEY_LON+" real not null, "+KEY_LAT+" real not null)";
    
    private static final String CREATE_STATION = 
    	"create table "+STATION_TABLE+" ("+KEY_ID+" integer primary key, "
    	+KEY_LOCATIONID+" integer not null, FOREIGN KEY("+KEY_LOCATIONID+") "
    	+"REFERENCES "+LOCATION_TABLE+"("+KEY_ID+"))";
    
    private static final String CREATE_VEHICLE = 
    	"create table "+VEHICLE_TABLE+" ("+KEY_ID+" integer primary key, "+KEY_BATT+" integer "
    	+"not null, "+KEY_LOCATIONID+" integer, "+KEY_TIME+" integer, "    +KEY_DESTID+" integer, "+KEY_SEATS 
    	+" integer not null, "+KEY_STATE+" text not null, "+KEY_ALT+" real not null, "
    	+"FOREIGN KEY("+KEY_LOCATIONID+") REFERENCES "+LOCATION_TABLE+"("+KEY_ID+"), "
    	+"FOREIGN KEY("+KEY_DESTID+") REFERENCES "+LOCATION_TABLE+"("+KEY_ID+"))";
    
    private static final String CREATE_TRAVELLER = 
    	"create table "+TRAVELLER_TABLE+" ("+KEY_ID+" integer primary key, "+KEY_STATE+" text not null, "
    	+KEY_DESTID+" integer not null, "+KEY_SIZE+" integer not null, "+KEY_NAME+" text not null, "
    	+KEY_STATIONID+" integer not null, "+KEY_CHECKIN+" integer not null, "+KEY_CHECKOUT+" integer, "
    	+"FOREIGN KEY("+KEY_DESTID+") REFERENCES "+LOCATION_TABLE+"("+KEY_ID+"), "
    	+"FOREIGN KEY("+KEY_STATIONID+") REFERENCES "+STATION_TABLE+"("+KEY_ID+"))";
    
    private static final String CREATE_TRIP = 
    	"create table "+TRIP_TABLE+" ("+KEY_VEHICLEID+" integer, "+KEY_TRAVELLERID+" integer, "
    	+"primary key("+KEY_VEHICLEID+", "+KEY_TRAVELLERID+"), "
    	+"FOREIGN KEY("+KEY_VEHICLEID+") REFERENCES "+VEHICLE_TABLE+"("+KEY_ID+"), "
    	+"FOREIGN KEY("+KEY_TRAVELLERID+") REFERENCES "+TRAVELLER_TABLE+"("+KEY_ID+"))";
    
   //o private static final String 

    private static final String DATABASE_NAME = "VehicleBD";
    private static final int DATABASE_VERSION = 2;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
        	super(context, DATABASE_NAME, null, DATABASE_VERSION);

          
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
        	
        	// Criar tabelas
            db.execSQL(CREATE_LOCATION);
            db.execSQL(CREATE_STATION);
            db.execSQL(CREATE_VEHICLE);
            db.execSQL(CREATE_TRAVELLER);
            db.execSQL(CREATE_TRIP);
            
            // inserir stations
            /*ContentValues locationValues = new ContentValues();
        	locationValues.put(KEY_ID, 1);
        	locationValues.put(KEY_NAME, "Aeroporto Portela");
        	locationValues.put(KEY_LON, -9.128523);
        	locationValues.put(KEY_LAT, 38.771149);
        	
        	ContentValues stationValues = new ContentValues();
        	
        	stationValues.put(KEY_ID, 1);
        	stationValues.put(KEY_NAME, "Aeroporto Portela");
        	stationValues.put(KEY_LOCATIONID, 1);
        	
        	db.insert(LOCATION_TABLE, null, locationValues);
        	db.insert(STATION_TABLE, null, stationValues);
        	
        	locationValues.put(KEY_ID, 2);
        	locationValues.put(KEY_NAME, "IST-Alameda");
        	locationValues.put(KEY_LON, -9.13831);
        	locationValues.put(KEY_LAT, 38.736762);
        	
        	stationValues.put(KEY_ID, 2);
        	stationValues.put(KEY_NAME, "IST-Alameda");
        	stationValues.put(KEY_LOCATIONID, 2);
        	
        	db.insert(LOCATION_TABLE, null, locationValues);
        	db.insert(STATION_TABLE, null, stationValues);
        	
        	locationValues.put(KEY_ID, 3);
        	locationValues.put(KEY_NAME, "IST-Taguspark");
        	locationValues.put(KEY_LON, -9.302748);
        	locationValues.put(KEY_LAT, 38.736854);
        	
        	stationValues.put(KEY_ID, 3);
        	stationValues.put(KEY_NAME, "IST-Taguspark");
        	stationValues.put(KEY_LOCATIONID, 3);
        	
        	db.insert(LOCATION_TABLE, null, locationValues);
        	db.insert(STATION_TABLE, null, stationValues);*/
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS locations");
            onCreate(db);
        }
        
    }

    public dbAdapter(Context ctx) {
        this.mCtx = ctx;
    }


    public dbAdapter open(int id) throws SQLException {
    	_id = id;
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    
    public void close() {
    	
    	// TODO limpar tabelas
    	mDb.execSQL("delete from "+VEHICLE_TABLE);
    	mDb.execSQL("delete from "+LOCATION_TABLE);
    	mDb.execSQL("delete from "+STATION_TABLE);
    	mDb.execSQL("delete from "+TRAVELLER_TABLE);
    	mDb.execSQL("delete from "+TRIP_TABLE);
        mDbHelper.close();
    }
    

    public long initiateVehicle(int batt, String state, int alt, int seats){
    	
    	ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_ID, _id);
        initialValues.put(KEY_BATT, batt);
        initialValues.put(KEY_STATE, state);
        initialValues.put(KEY_ALT, alt);
        initialValues.put(KEY_SEATS, seats);
        
        //initialValues.put(KEY_TIME, System.currentTimeMillis());
        
        return mDb.insert(VEHICLE_TABLE, null, initialValues);
    }
    
    public int getBattery(){
    	
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_BATT}, 
				KEY_ID+"="+_id, null,null, null, null, null);
    	mCursor.moveToFirst();
    	
    	int ret = mCursor.getInt(0);
    	mCursor.close();
    	return ret;
    }
    
    public String getState(){
    	
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_STATE}, 
				KEY_ID+"="+_id, null,null, null, null, null);
    	mCursor.moveToFirst();
    	
    	String ret = mCursor.getString(0);
    	mCursor.close();
    	return ret;
    }
    
    public void setBattery(int batt){
    	
    	if(batt == 0){
    		
    		setState("OutOfBattery");
    	}else{
    		
    		setState("onStation");
    	}
    	
    	ContentValues Values = new ContentValues();
        Values.put(KEY_BATT, batt);
    	
    	mDb.update(VEHICLE_TABLE, Values, KEY_ID +" = "+ _id, null);
    	
    }
    
    /*public int getBattery(){
    	
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_BATT}, KEY_ID + "=" + _id, null,
                    				null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	return mCursor.getInt(0);
    	
    }*/
    
    public void setState(String state){
    	
    	ContentValues Values = new ContentValues();
        Values.put(KEY_STATE, state);
    	
    	mDb.update(VEHICLE_TABLE, Values, KEY_ID +" = "+ _id, null);
    	
    }
    
    public void setVehicleLocation(Location loc){
    
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_LOCATIONID}, 
    						KEY_ID+"="+_id, null,null, null, null, null);
    	mCursor.moveToFirst();
    	
    	ContentValues Values = new ContentValues();
    	
    	if(mCursor.isNull(0)){
    		
            Values.put(KEY_NAME, "current");
            Values.put(KEY_LON, loc.getLongitude());
            Values.put(KEY_LAT, loc.getLatitude());         
            ContentValues Values2 = new ContentValues();
            Values2.put(KEY_LOCATIONID, mDb.insert(LOCATION_TABLE, null, Values));
            
            mDb.update(VEHICLE_TABLE, Values2, KEY_ID +" = "+ _id, null);
    		
    	}else{

    		Values.put(KEY_LON, loc.getLongitude());
            Values.put(KEY_LAT, loc.getLatitude());
            
            mDb.update(LOCATION_TABLE, Values, KEY_ID +" = "+ mCursor.getInt(0), null);
    	}
    	
    	mCursor.close();
	}
    
    public void setDestination(String name, Location loc){
        
    	ContentValues Values = new ContentValues();
        Values.put(KEY_NAME, name);
        Values.put(KEY_LON, loc.getLongitude());
        Values.put(KEY_LAT, loc.getLatitude());
        
        ContentValues Values2 = new ContentValues();
        Values2.put(KEY_DESTID, mDb.insert(LOCATION_TABLE, null, Values));
        
        mDb.update(VEHICLE_TABLE, Values2, KEY_ID +" = "+ _id, null);
	}
    
    public void checkoutTraveller(int id, long time){
    	
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_SEATS}, 
    			KEY_ID+"="+_id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	int seats = mCursor.getInt(0);
    	
    	mCursor.close();
    	
    	mCursor = mDb.query(true, TRAVELLER_TABLE, new String[] {KEY_SIZE}, 
    			KEY_ID+"="+id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	seats += mCursor.getInt(0);
    	
    	mCursor.close();
    	
    	ContentValues values = new ContentValues();
    	values.put(KEY_CHECKOUT, time);
    	values.put(KEY_STATE, "arrived");
    	
    	mDb.update(TRAVELLER_TABLE, values, KEY_ID +" = "+ id, null);
    	
    	values.clear();
    	
    	values.put(KEY_SEATS, seats);
    	
    	mDb.update(VEHICLE_TABLE, values, KEY_ID +" = "+ _id, null);
    	
    }
    
    private boolean hasStation(int id){
    	
    	Cursor mCursor = mDb.query(true, STATION_TABLE, new String[] {KEY_ID}, 
    			KEY_ID+"="+id, null,null, null, null, null);
		
    	boolean ret;
    	
		if(mCursor.getCount() == 0)
			ret = false;
		else
			ret = true;
		
		mCursor.close();
		return ret;
    }
    
    public Location getVehicleLocation(){
    	
    	Location ret = new Location("currentLocation");
    	
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_LOCATIONID}, 
    			KEY_ID+"="+_id, null,null, null, null, null);
    	mCursor.moveToFirst();
    	
    	int location_id = mCursor.getInt(0);
    	
    	mCursor.close();
    	
    	mCursor = mDb.query(true, LOCATION_TABLE, new String[] {KEY_LON, KEY_LAT}, 
    			KEY_ID+"="+location_id, null,null, null, null, null);
    	mCursor.moveToFirst();
    	
    	ret.setLongitude(mCursor.getDouble(0));
    	ret.setLatitude(mCursor.getDouble(1));
    	mCursor.close();
    	
    	return ret;
    }
    
    
    public void setTrip(Trip trip){
    	
    	
    	Cursor mCursor = mDb.query(true, TRIP_TABLE, new String[] {KEY_TRAVELLERID}, 
    			KEY_VEHICLEID+"="+_id, null,null, null, null, null);
		mCursor.moveToFirst();
		
		if(mCursor.getCount()!=0){
		
			do{
				
				Cursor mCursor3 = mDb.query(true, TRAVELLER_TABLE, new String[] {KEY_DESTID}, 
		    			KEY_ID+"="+mCursor.getInt(0), null,null, null, null, null);
				mCursor3.moveToFirst();
				
		    	mDb.execSQL("delete from "+LOCATION_TABLE+" where "+KEY_ID+"="+mCursor3.getInt(0));
		    	mCursor3.close();
		    	
		    	mDb.execSQL("delete from "+TRAVELLER_TABLE+" where "+KEY_ID+"="+mCursor.getInt(0));
		    	
		    	
			}while(mCursor.moveToNext());
			
			mDb.execSQL("delete from "+TRIP_TABLE+" where "+KEY_VEHICLEID+"="+_id);
		
		}
		mCursor.close();
    	
    	
    	mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_SEATS}, 
    			KEY_ID+"="+_id, null,null, null, null, null);
    	
    	mCursor.moveToFirst();
    	
    	int seats = mCursor.getInt(0);
    	
    	mCursor.close();
    	
    	ContentValues v_trav = new ContentValues();
    	ContentValues v_loc;
    	ContentValues v_stat;
    	ContentValues v_trip = new ContentValues();
    	for(Traveller trav : trip.getTravellersList()){
    		
    		
    		v_loc = new ContentValues();
    		v_loc.put(KEY_NAME, trav.getDestination().getName());
    		v_loc.put(KEY_LON, trav.getDestination().getLon());
    		v_loc.put(KEY_LAT, trav.getDestination().getLat());

    		v_trav.put(KEY_DESTID, mDb.insert(LOCATION_TABLE, null, v_loc));
    		v_trav.put(KEY_SIZE, trav.getSize());
    		seats -= trav.getSize();
    		v_trav.put(KEY_NAME, trav.getName());
    		v_trav.put(KEY_ID, trav.getId());
    		v_trav.put(KEY_STATE, trav.getState());
    		v_trav.put(KEY_CHECKIN, trav.getCheckin());
    		
    		v_stat = new ContentValues();
    		v_stat.put(KEY_ID, trav.getStation().getId());
    		
    		/** STATION **/
    		
    		if(!hasStation(trav.getStation().getId())){
	    		v_stat = new ContentValues();
	    		v_stat.put(KEY_ID, trav.getStation().getId());
	    		
	    		v_loc = new ContentValues();
	    		v_loc.put(KEY_NAME, trav.getStation().getLocation().getName());
	    		v_loc.put(KEY_LON, trav.getStation().getLocation().getLon());
	    		v_loc.put(KEY_LAT, trav.getStation().getLocation().getLat());
	    		
	    		v_stat.put(KEY_LOCATIONID, mDb.insert(LOCATION_TABLE, null, v_loc));
	    		
	    		v_trav.put(KEY_STATIONID, mDb.insert(STATION_TABLE, null, v_stat));
    		}
    		else
    			v_trav.put(KEY_STATIONID, trav.getStation().getId());
    		
    		v_trip.put(KEY_TRAVELLERID, mDb.insert(TRAVELLER_TABLE, null, v_trav));
    		v_trip.put(KEY_VEHICLEID, _id);
    		
    		mDb.insert(TRIP_TABLE, null, v_trip);
    		
    		v_trip.clear();
    		v_loc.clear();
    		v_trav.clear();
    	}
    	
    	v_trip.put(KEY_SEATS, seats);
    	
    	mDb.update(VEHICLE_TABLE, v_trip, KEY_ID +" = "+ _id, null);
    	
    }
    
public void updateDB(BdMsg DB){
    	
    	int vehicle_id;
    	Cursor mCursor;
    	
    	System.out.println("Entra "+DB.getTripsCount());
       	
    	for(Trip trip : DB.getTripsList()){
    		
    		vehicle_id = trip.getVehicle().getId();
    		System.out.println("other ID: "+ vehicle_id);
    		System.out.println("this ID: "+ _id);
    		
    		if(vehicle_id != _id){
    			
    			System.out.println("entra no if");
    		
    			mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_TIME}, 
    	    			KEY_ID+"="+vehicle_id, null,null, null, null, null);
    			
    			mCursor.moveToFirst();
    			
    			int status = 0;
    			
    			if(mCursor.getCount() == 0)
    				/* Not Exists */
    				status = 1;
    			else{
    				if(mCursor.getLong(0) < trip.getTime())
    					/* Old */
    					status = 2;
    			}
    			System.out.println("time: "+ trip.getTime());
    			System.out.println("status: "+status);
    			
    			ContentValues vehicle_values = new ContentValues();
    			
    			if(status > 0){
    			
    				if(status == 2){
    					
    					/** Clean Vehicle**/
    					mDb.execSQL("delete from "+VEHICLE_TABLE+" where "+KEY_ID+"="+vehicle_id);
    					
    					/** Clean Travelers **/
    					Cursor mCursor2 = mDb.query(true, TRIP_TABLE, new String[] {KEY_TRAVELLERID}, 
    	    	    			KEY_VEHICLEID+"="+vehicle_id, null,null, null, null, null);
    					mCursor2.moveToFirst();
    					
    					do{
    						
	    					Cursor mCursor3 = mDb.query(true, TRAVELLER_TABLE, new String[] {KEY_DESTID}, 
	    	    	    			KEY_ID+"="+mCursor2.getInt(0), null,null, null, null, null);
	    					mCursor3.moveToFirst();
	    					
	    			    	mDb.execSQL("delete from "+LOCATION_TABLE+" where "+KEY_ID+"="+mCursor3.getInt(0));
	    			    	mCursor3.close();
	    			    	
	    			    	mDb.execSQL("delete from "+TRAVELLER_TABLE+" where "+KEY_ID+"="+mCursor2.getInt(0));
	    			    	
	    			    	
    					}while(mCursor2.moveToNext());
    					
    					mDb.execSQL("delete from "+TRIP_TABLE+" where "+KEY_VEHICLEID+"="+vehicle_id);
    					mCursor2.close();
    				}
    			
    				System.out.println("Entra no update");
    				
    				vehicle v = trip.getVehicle();
					
					/** Vehicle values **/
					vehicle_values.put(KEY_ID,v.getId());
					vehicle_values.put(KEY_BATT,v.getBattery());
					vehicle_values.put(KEY_TIME,trip.getTime());
					vehicle_values.put(KEY_SEATS,v.getSeatsAvailable());
					vehicle_values.put(KEY_STATE,v.getState());
					vehicle_values.put(KEY_ALT,v.getCurrentAltitude());
					
					/** Location values **/
					ContentValues location_values = new ContentValues();
					location_values.put(KEY_NAME, v.getCurrentLocation().getName());
					location_values.put(KEY_LON, v.getCurrentLocation().getLon());
					location_values.put(KEY_LAT, v.getCurrentLocation().getLat());
					vehicle_values.put(KEY_LOCATIONID,mDb.insert(LOCATION_TABLE, null, location_values));
					
					/** DEstination Values **/
					location_values = new ContentValues();
					location_values.put(KEY_NAME, v.getNextDestination().getName());
					location_values.put(KEY_LON, v.getNextDestination().getLon());
					location_values.put(KEY_LAT, v.getNextDestination().getLat());
					vehicle_values.put(KEY_DESTID,mDb.insert(LOCATION_TABLE, null, location_values));
					
					mDb.insert(VEHICLE_TABLE, null, vehicle_values);
					
					/** Add Travellers **/
    				for(Traveller trav : trip.getTravellersList()){
    					
    					ContentValues traveller_values = new ContentValues();
    					/** Traveller values **/
    					traveller_values.put(KEY_ID, trav.getId());
    					traveller_values.put(KEY_STATE, trav.getState());
    					traveller_values.put(KEY_SIZE, trav.getSize());
    					traveller_values.put(KEY_NAME, trav.getName());
    					traveller_values.put(KEY_CHECKIN, trav.getCheckin());
    					if(trav.hasCheckout()){
    						traveller_values.put(KEY_CHECKOUT, trav.getCheckout());
    					}
    					
    					/** Traveller destination **/
    					location_values = new ContentValues();
    					location_values.put(KEY_NAME, trav.getDestination().getName());
    					location_values.put(KEY_LON, trav.getDestination().getLon());
    					location_values.put(KEY_LAT, trav.getDestination().getLat());
    					
    					traveller_values.put(KEY_DESTID, mDb.insert(LOCATION_TABLE, null, location_values));
    					
    					/** Traveller Station **/
    					if(!hasStation(trav.getStation().getId())){
    						
    			    		ContentValues station_values = new ContentValues();
    			    		station_values.put(KEY_ID, trav.getStation().getId());
    			    		
    			    		location_values = new ContentValues();
    			    		location_values.put(KEY_NAME, trav.getStation().getLocation().getName());
    			    		location_values.put(KEY_LON, trav.getStation().getLocation().getLon());
    			    		location_values.put(KEY_LAT, trav.getStation().getLocation().getLat());
    			    		
    			    		station_values.put(KEY_LOCATIONID, mDb.insert(LOCATION_TABLE, null, location_values));
    			    		
    			    		traveller_values.put(KEY_STATIONID, mDb.insert(STATION_TABLE, null, station_values));
    			    		
    			    		
    		    		}
    		    		else
    		    			traveller_values.put(KEY_STATIONID, trav.getStation().getId());
    					
    					ContentValues trip_values = new ContentValues();
    					trip_values.put(KEY_VEHICLEID, vehicle_id);
    					trip_values.put(KEY_TRAVELLERID, mDb.insert(TRAVELLER_TABLE, null, traveller_values));
    					mDb.insert(TRIP_TABLE, null, trip_values);
    					
    				}
    				
    			}
    				
    			System.out.println("Faz o update");
    			mCursor.close();
    			
    		}
    	}
    }
    
    /** Central Server version **/
	
public ArrayList<smartfleet.mobile.trip.Trip> getDBtoCentral(){
	
	System.out.println("FAZ 1"); //TODO
	
	ArrayList<smartfleet.mobile.trip.Trip> trips = new ArrayList<smartfleet.mobile.trip.Trip>();
	
	smartfleet.mobile.trip.Trip trip;
	smartfleet.mobile.vehicle.Vehicle v;
	smartfleet.mobile.location.Location loc;
	
	Cursor mCursor;
	Cursor allBd = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_ID, KEY_BATT, KEY_LOCATIONID,
			KEY_DESTID, KEY_SEATS, KEY_STATE, KEY_ALT, KEY_TIME}, null, null,
			null, null, null, null);
	
	if(allBd.getCount() != 0){

    	allBd.moveToFirst();
    	
    	
    	do{
    		System.out.println("FAZ 2"); //TODO
    		trip = new smartfleet.mobile.trip.Trip();
	    	
	    	/** Vehicle data **/
	    	
	    	
	    	v = new smartfleet.mobile.vehicle.Vehicle(
	    			String.valueOf(allBd.getInt(0)), 
	    			String.valueOf(allBd.getInt(1)),
	    			allBd.getString(5),
	    			String.valueOf(allBd.getInt(4)));
	    	
	    	v.setCurrentAltitude(allBd.getDouble(6));
	    	
	    	trip.setIdTrip(allBd.getInt(0));
	    	
	    	if(!allBd.isNull(3)){
	    		
	    		System.out.println("FAZ 3"); //TODO
	    		
	    		int dest_id = allBd.getInt(3);
	    		
	    		mCursor = mDb.query(true, LOCATION_TABLE, new String[] {KEY_NAME, KEY_LON, KEY_LAT}, 
		    			KEY_ID+"="+dest_id, null, null, null, null, null);
		    	mCursor.moveToFirst();
		    	
		    	/** vehicle destination **/
		    	loc = new smartfleet.mobile.location.Location(
		    			String.valueOf(dest_id), 
		    			mCursor.getString(0), 
		    			String.valueOf(mCursor.getDouble(2)), 
		    			String.valueOf(mCursor.getDouble(1)));
		    	
		    	v.setNextDestination(loc);
		    	
		    	mCursor.close();
	    		
	    	}
	    	

	    	
	    	mCursor = mDb.query(true, LOCATION_TABLE, new String[] {KEY_NAME, KEY_LON, KEY_LAT}, 
	    			KEY_ID+"="+allBd.getInt(2), null, null, null, null, null);
	    	mCursor.moveToFirst();
	    	
	    	/** Current location of vehicle */
	    	loc = new smartfleet.mobile.location.Location(
	    			String.valueOf(allBd.getInt(2)),
	    			mCursor.getString(0),
	    			String.valueOf(mCursor.getDouble(2)),
	    			String.valueOf(mCursor.getDouble(1)));
	    	
	    	v.setCurrentLocation(loc);
	    	
	    	mCursor.close();
	    	
	    	trip.setVehicle(v);
	
	    	mCursor = mDb.query(true, TRIP_TABLE, new String[] {KEY_TRAVELLERID}, 
	    			KEY_VEHICLEID+"="+allBd.getInt(0), null, null, null, null, null);
	    	
	    	/** Vehicle travellers **/
	    	
	    	if(mCursor.getCount() != 0){
	    		
	    		Cursor mCursor2, mCursor3, mCursor4;
	    		
	    		mCursor.moveToFirst();
	    		
	    		smartfleet.mobile.traveller.Traveller t;
	    		smartfleet.mobile.station.Station sta;
	    		ArrayList<smartfleet.mobile.traveller.Traveller> travellers =
	    			new ArrayList<smartfleet.mobile.traveller.Traveller>();
	    		
	    		do{
	    			System.out.println("FAZ 4"); //TODO
	    			
	    			mCursor2 = mDb.query(true, TRAVELLER_TABLE, new String[] {KEY_ID, KEY_STATE, KEY_DESTID,
	    					KEY_SIZE, KEY_NAME, KEY_STATIONID, KEY_CHECKIN, KEY_CHECKOUT}, 
	    	    			KEY_ID+"="+mCursor.getInt(0), null,null, null, null, null);
	    			mCursor2.moveToFirst();
	    			
	    			/** Traveller data **/		
	    			t =new smartfleet.mobile.traveller.Traveller(
	    					String.valueOf(mCursor2.getInt(0)),
	    					mCursor2.getString(4),
	    					mCursor2.getString(1),
	    					String.valueOf(mCursor2.getInt(3)),
	    					String.valueOf(mCursor2.getLong(6)),
	    					String.valueOf(mCursor2.getLong(7)));
	    			
	    			/** Traveller station */
	    			mCursor3 = mDb.query(true, STATION_TABLE, new String[] {KEY_LOCATIONID}, 
	    	    			KEY_ID+"="+mCursor2.getInt(5), null,
	    					null, null, null, null);
	    			mCursor3.moveToFirst();
	    			
	    			sta = new smartfleet.mobile.station.Station();
	    			sta.setIdStation(mCursor2.getInt(5));
	    			
	    			mCursor4 = mDb.query(true, LOCATION_TABLE, new String[] {KEY_NAME, KEY_LON, KEY_LAT}, 
	    	    			KEY_ID+"="+mCursor3.getInt(0), null,
	    					null, null, null, null);
	    			mCursor4.moveToFirst();
	    			
	    			loc = new smartfleet.mobile.location.Location(
	    					String.valueOf(mCursor3.getInt(0)),
	    					mCursor4.getString(0),
	    					String.valueOf(mCursor4.getDouble(2)),
	    					String.valueOf(mCursor4.getDouble(1)));
	    			
	    			
	    			mCursor4.close();
	    			
	    			sta.setLocation(loc);
	    			
	    			t.setStation(sta);
	    			
	    			mCursor3.close();
	    			
	    			/** Traveller Destinaton **/
	    			mCursor3 = mDb.query(true, LOCATION_TABLE, new String[] {KEY_NAME, KEY_LON, KEY_LAT}, 
	    	    			KEY_ID+"="+mCursor2.getInt(2), null, null, null, null, null);
	    			mCursor3.moveToFirst();

	    			loc = new smartfleet.mobile.location.Location(
	    					String.valueOf(mCursor2.getInt(2)),
	    					mCursor3.getString(0),
	    					String.valueOf(mCursor3.getDouble(2)),
	    					String.valueOf(mCursor3.getDouble(1)));
	    			
	    			t.setDestination(loc);
	    			
	    			travellers.add(t);
	    			
	    			mCursor3.close();
	    			mCursor2.close();
	    			
	    		}while(mCursor.moveToNext());
	    		
	    		System.out.println("FAZ 5"); //TODO
	    		
	    		trip.setTravellers(travellers);
	    	}
	    	
	    	mCursor.close();
	    	
	    	if(trip.getIdTrip() == _id)
	    		trip.setInfoTimeStap(new Date(System.currentTimeMillis()));
    		else
    			trip.setInfoTimeStap(new Date(allBd.getLong(7)));
	    	
	    	
	    	trips.add(trip);
	    	
    	}while(allBd.moveToNext());
    	
    	System.out.println("FAZ 6"); //TODO
	}
	
	allBd.close();
	
	return trips;
	
}


	/** Proto version **/
    public BdMsg getDB(){
    	
    	BdMsg.Builder bdmsg = BdMsg.newBuilder();
    	
    	
    	Trip.Builder trip;
    	location.Builder loc;
    	
    	Cursor mCursor;
    	Cursor allBd = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_ID, KEY_BATT, KEY_LOCATIONID,
    			KEY_DESTID, KEY_SEATS, KEY_STATE, KEY_ALT, KEY_TIME}, null, null,
				null, null, null, null);
    	
    	if(allBd.getCount() != 0){
    
	    	allBd.moveToFirst();
	    	
	    	do{
	    		
	    		trip = Trip.newBuilder();
	    		
		    	vehicle.Builder v = vehicle.newBuilder();
		    	
		    	/** Vehicle data **/
		    	v.setId(allBd.getInt(0));
		    	trip.setId(v.getId());
		    	v.setBattery(allBd.getInt(1));
		    	v.setSeatsAvailable(allBd.getInt(4));
		    	v.setState(allBd.getString(5));
		    	v.setCurrentAltitude(allBd.getDouble(6));
		    	
		    	if(!allBd.isNull(3)){
		    		
		    		int dest_id = allBd.getInt(3);
		    		
		    		mCursor = mDb.query(true, LOCATION_TABLE, new String[] {KEY_NAME, KEY_LON, KEY_LAT}, 
			    			KEY_ID+"="+dest_id, null, null, null, null, null);
			    	mCursor.moveToFirst();
			    	
			    	/** vehicle destination **/
			    	loc = location.newBuilder();
			    	loc.setName(mCursor.getString(0));
			    	loc.setLon(mCursor.getDouble(1));
			    	loc.setLat(mCursor.getDouble(2));
			    	v.setNextDestination(loc);
			    	
			    	mCursor.close();
		    		
		    	}
		    	

		    	
		    	mCursor = mDb.query(true, LOCATION_TABLE, new String[] {KEY_NAME, KEY_LON, KEY_LAT}, 
		    			KEY_ID+"="+allBd.getInt(2), null, null, null, null, null);
		    	mCursor.moveToFirst();
		    	
		    	/** Current location of vehicle */
		    	loc = location.newBuilder();
		    	loc.setName(mCursor.getString(0));
		    	loc.setLon(mCursor.getDouble(1));
		    	loc.setLat(mCursor.getDouble(2));
		    	v.setCurrentLocation(loc);
		    	
		    	mCursor.close();
		    	
		    	trip.setVehicle(v);
		
		    	mCursor = mDb.query(true, TRIP_TABLE, new String[] {KEY_TRAVELLERID}, 
		    			KEY_VEHICLEID+"="+allBd.getInt(0), null, null, null, null, null);
		    	
		    	/** Vehicle travellers **/
		    	
		    	if(mCursor.getCount() != 0){
		    		
		    		Cursor mCursor2, mCursor3, mCursor4;
		    		
		    		mCursor.moveToFirst();
		    		Traveller.Builder t;
		    		Station.Builder sta;
		    		
		    		do{
		    			
		    			mCursor2 = mDb.query(true, TRAVELLER_TABLE, new String[] {KEY_ID, KEY_STATE, KEY_DESTID,
		    					KEY_SIZE, KEY_NAME, KEY_STATIONID, KEY_CHECKIN, KEY_CHECKOUT}, 
		    	    			KEY_ID+"="+mCursor.getInt(0), null,null, null, null, null);
		    			mCursor2.moveToFirst();
		    			
		    			/** Traveller data **/
		    			t = Traveller.newBuilder();
		    			t.setId(mCursor2.getInt(0));
		    			t.setState(mCursor2.getString(1));
		    			t.setSize(mCursor2.getInt(3));
		    			t.setName(mCursor2.getString(4));
		    			t.setCheckin(mCursor2.getLong(6));
		    			t.setCheckout(mCursor2.getLong(7));
		    			
		    			
		    			/** Traveller station */
		    			mCursor3 = mDb.query(true, STATION_TABLE, new String[] {KEY_LOCATIONID}, 
		    	    			KEY_ID+"="+mCursor2.getInt(5), null,
		    					null, null, null, null);
		    			mCursor3.moveToFirst();
		    			
		    			sta = Station.newBuilder();
		    			sta.setId(mCursor2.getInt(5));
		    			
		    			mCursor4 = mDb.query(true, LOCATION_TABLE, new String[] {KEY_NAME, KEY_LON, KEY_LAT}, 
		    	    			KEY_ID+"="+mCursor3.getInt(0), null,
		    					null, null, null, null);
		    			mCursor4.moveToFirst();
		    			loc = location.newBuilder();
		    			loc.setName(mCursor4.getString(0));
		    			loc.setLon(mCursor4.getDouble(1));
		    			loc.setLat(mCursor4.getDouble(2));
		    			
		    			mCursor4.close();
		    			
		    			sta.setLocation(loc);
		    			t.setStation(sta);
		    			
		    			mCursor3.close();
		    			
		    			/** Traveller Destinaton **/
		    			mCursor3 = mDb.query(true, LOCATION_TABLE, new String[] {KEY_NAME, KEY_LON, KEY_LAT}, 
		    	    			KEY_ID+"="+mCursor2.getInt(2), null, null, null, null, null);
		    			mCursor3.moveToFirst();
		    			loc = location.newBuilder();
		    			loc.setName(mCursor3.getString(0));
		    			loc.setLon(mCursor3.getDouble(1));
		    			loc.setLat(mCursor3.getDouble(2));
		    			
		    			t.setDestination(loc);
		    			
		    			trip.addTravellers(t);
		    			
		    			mCursor3.close();
		    			mCursor2.close();
		    			
		    		}while(mCursor.moveToNext());
		    		
		    	}
		    	
		    	mCursor.close();
		    	
		    	if(trip.getId() == _id)
	    			trip.setTime(System.currentTimeMillis());
	    		else
	    			trip.setTime(allBd.getLong(7));
		    	
		    	bdmsg.addTrips(trip);
		    	
	    	}while(allBd.moveToNext());
	    	
	    	
    	}
    	
    	allBd.close();
    	
    	return bdmsg.build();
    	
    	
    }
    
    public double getAltitude(){
    	
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_ALT}, 
    			KEY_ID+"="+_id, null, null, null, null, null);
    	mCursor.moveToFirst();
    	
    	double ret = mCursor.getDouble(0);
    	
    	mCursor.close();
    	
    	return ret;
    }
    
    public void setAltitude(double alt){
    	
    	ContentValues Values = new ContentValues();
        Values.put(KEY_ALT, alt);
    	
    	mDb.update(VEHICLE_TABLE, Values, KEY_ID +" = "+ _id, null);
    	
    }
    
    public Vehicle getVehicle(){
    	
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_BATT, KEY_LOCATIONID,
    			 KEY_SEATS, KEY_STATE, KEY_ALT}, KEY_ID+"="+_id, null, null, null, null, null);
    	mCursor.moveToFirst();
    	
    	Cursor mCursor2 = mDb.query(true, LOCATION_TABLE, new String[] {KEY_NAME, KEY_LON, KEY_LON}, 
    			KEY_ID+"="+mCursor.getInt(1), null, null, null, null, null);
    	mCursor2.moveToFirst();
    	
    	smartfleet.mobile.location.Location loc = new smartfleet.mobile.location.Location(
    			String.valueOf(mCursor.getInt(1)),
    			mCursor.getString(0),
    			String.valueOf(mCursor.getDouble(2)),
    			String.valueOf(mCursor.getDouble(1)));
    	mCursor2.close();
    	
    	Vehicle v = new Vehicle();
    	
    	v.setIdVehicle(_id);
    	v.setCurrentLocation(loc);
    	v.setBatteryLevel(mCursor.getInt(0));
    	v.setSeatsAvailable(mCursor.getInt(2));
    	v.setState(mCursor.getString(3));
    	v.setCurrentAltitude(mCursor.getDouble(4));
    	
    	mCursor.close();
    	
    	return v;
    }
 
    public void printDB(){
    	
    	Cursor mCursor2;
    	
    	Cursor mCursor = mDb.query(true, VEHICLE_TABLE, new String[] {KEY_ID, KEY_BATT, KEY_LOCATIONID, KEY_TIME,
    			KEY_DESTID, KEY_SEATS, KEY_STATE, KEY_ALT}, null, null, null, null, null, null);
    	
    	mCursor.moveToFirst();
    	do{
    		System.out.println("--------------------");
    		System.out.println("Vehicle "+mCursor.getInt(0));
    		System.out.println("State: "+mCursor.getString(6));
    		System.out.println("Battery: "+mCursor.getInt(1));
    		System.out.println("Time: "+mCursor.getInt(3));
    		System.out.println("Seats: "+mCursor.getInt(5));
    		
    		mCursor2 = mDb.query(true, TRIP_TABLE, new String[] {KEY_TRAVELLERID}, 
    				KEY_VEHICLEID+"="+mCursor.getInt(0), null, null, null, null, null);
    		
    		if(mCursor2.getCount() != 0){
    			
    			Cursor mCursor3;
    			
    			System.out.println("Travellers:");
    			mCursor2.moveToFirst();
    			
    			do{
    				mCursor3 = mDb.query(true, TRAVELLER_TABLE, new String[] {KEY_NAME, KEY_CHECKIN, KEY_CHECKOUT}, 
    						KEY_ID+"="+mCursor2.getInt(0), null, null, null, null, null);
    				mCursor3.moveToFirst();
    				System.out.println("Traveller "+mCursor3.getString(0));
    				System.out.println("Checkin: "+mCursor3.getLong(1));
    				System.out.println("Checkout: "+mCursor3.getLong(2));
    				mCursor3.close();
    				
    			}while(mCursor2.moveToNext());
    			
    			
    		}

    		mCursor2.close();
    		
    	}while(mCursor.moveToNext());
    	
    	mCursor.close();
    }
}